package ro.msg.quiz.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import ro.msg.quiz.domain.MultipleChoiceQuestion;
import ro.msg.quiz.domain.Question;
import ro.msg.quiz.domain.QuestionType;
import ro.msg.quiz.domain.Quiz;
import ro.msg.quiz.domain.SingleChoiceQuestion;
import ro.msg.quiz.domain.TextQuestion;

//AUTHORS: Ervin, Patricia

public class QuizDB {

    public static void createQuiz(String quizName) throws ClassNotFoundException, SQLException {
	String sql = "INSERT into quizzes (name) values ('" + quizName + "')";
	DatabaseCon.getInstance().executeUpdate(sql);
    }

    public static void deleteQuiz(String quizName) throws ClassNotFoundException, SQLException {
	String sql = "DELETE from quizzes where name = '" + quizName + "'";
	DatabaseCon.getInstance().executeUpdate(sql);
    }

    public static ResultSet getQuizzes() throws ClassNotFoundException, SQLException {
	String sql = "SELECT * from quizzes";
	return DatabaseCon.getInstance().executeQuery(sql);
    }

    public static int getQuizID(String quizName) throws ClassNotFoundException, SQLException {
	String sql = "SELECT id from quizzes where name = '" + quizName + "' ";
	ResultSet rs = DatabaseCon.getInstance().executeQuery(sql);
	rs.next();
	return rs.getInt(1);
    }

    public static List<Quiz> populateQuizzes() throws Exception {
	Connection con = null;
	con = DatabaseCon.getInstance().getConnection();
	List<Quiz> quizzesList = new ArrayList<Quiz>();

	Statement s1 = con.createStatement();
	s1.setMaxRows(100);
	ResultSet rs = s1.executeQuery("select * from " + "quizzes");

	while (rs.next()) {
	    int id = rs.getInt(1);
	    String name = rs.getString(2);
	    Quiz quiz = new Quiz(id, name);

	    Statement s2 = con.createStatement();
	    ResultSet rsRel = s2.executeQuery("select * from relations where idQuiz =" + Integer.toString(id));
	    while (rsRel.next()) {

		int questionId = rsRel.getInt(1);
		Statement s3 = con.createStatement();
		ResultSet rsQuestion = s3.executeQuery("select * from questions where id =" + Integer.toString(questionId));
		rsQuestion.next();

		Statement s4 = con.createStatement();
		ResultSet rsAnsw = s4.executeQuery("select * from answers where idQuestion =" + Integer.toString(questionId));
		String questionText = rsQuestion.getString(2);
		List<String> options = new ArrayList<String>();
		if (rsQuestion.getObject(3).equals(QuestionType.SINGLE_CHOICE_Q.toString())) {
		    int correctIndex = 0;
		    int index = -1;
		    while (rsAnsw.next()) {
			index++;
			options.add(rsAnsw.getString(3));
			if (rsAnsw.getBoolean(4) == true) {
			    correctIndex = index;
			}
		    }
		    Question q = new SingleChoiceQuestion(id, questionText, options, correctIndex);
		    quiz.addQuestion(q);
		} else if (rsQuestion.getObject(3).equals(QuestionType.MULTIPLE_CHOICE_Q.toString())) {
		    int index = -1;
		    ArrayList<Integer> correctIndex = new ArrayList<Integer>();
		    while (rsAnsw.next()) {
			index++;
			options.add(rsAnsw.getString(3));
			if (rsAnsw.getBoolean(4) == true) {
			    correctIndex.add(index);
			}
		    }
		    Question q = new MultipleChoiceQuestion(id, questionText, options, correctIndex);
		    quiz.addQuestion(q);
		} else {
		    rsAnsw.next();
		    String answ = rsAnsw.getString(3);
		    Question q = new TextQuestion(id, questionText, answ);
		    quiz.addQuestion(q);
		}
	    }
	    quizzesList.add(quiz);
	}

	return quizzesList;
    }

}
